import pandas as pd
import os

sale_list = pd.DataFrame()
mcf_list = pd.DataFrame()

# print(os.getcwd())
#
p_path = os.getcwd()
dir_path = p_path + "/数据源/ERP数据/"
# amz_dir_path = p_path+"/数据源/AMZ后台报告/"
#
# amz_list_temp = []
# for fname in os.listdir(amz_dir_path):
#     if fname.endswith(".txt"):
#         data = pd.read_csv(amz_list_temp+fname,sep='\t',header=None)
#         amz_list_temp.append(data)
#
# amz_list = pd.concat(amz_list_temp)
# amz_list.to_excel(f"{p_path}/处理结果/4.1AMZ配送明细.xlsx", index=False)


for fname in os.listdir(dir_path):
    # 通过发货单匹配领星系统编号
    if fname.startswith('仓库发货') and fname.endswith(".xlsx"):
        sale_list = pd.read_excel(dir_path + fname)
        print(f'仓库发货订单数据：{len(sale_list)} 条')
        sale_list = sale_list[
            ["销售出库单号", "平台单号", "SKU", "数量"]]
        pd.set_option('display.max_rows', 120, 'display.max_columns', None)
        # da = sale_list['销售出库单号'] = sale_list['销售出库单号'].ffill()
        # da = sale_list.ffill(axis=1,limit=1)
        sale_list = sale_list.ffill()
        print(f'仓库发货订单去重复之后的数据：{len(sale_list)} 条')
        # print('------------------sale_list---------------------')
        # print(sale_list.iloc[4390:4400])

    if fname.startswith('多渠道订单') and fname.endswith(".xlsx"):
        mcf_list = pd.read_excel(dir_path + fname)
        print(f'多渠道发货订单数据：{len(mcf_list)} 条')
        # print('------------------mcf_list---------------------')
        # print(mcf_list)
        # ddd = mcf_list.groupby(['卖家订单号','MSKU'])['FBA费']/mcf_list.groupby(['卖家订单号','MSKU'])['数量']
        mcf_list['FBA DIV'] = mcf_list['FBA费'].div(mcf_list['数量'])

        mcf_list = mcf_list[
            ["卖家订单号", "亚马逊订单号", "MSKU", "SKU", "数量", "FBA费", "FBA DIV"]]
        # print(mcf_list)

pr = pd.merge(sale_list, mcf_list, how='outer', left_on=['销售出库单号', 'SKU'], right_on=['卖家订单号', 'SKU'])
# print('------------------pr---------------------')
# print(pr.iloc[1630:1640])
pr = pr[["销售出库单号", "平台单号", "亚马逊订单号", "MSKU", "SKU", "FBA费", "FBA DIV"]]
pr.to_excel(f"{p_path}/处理过的数据/4.领星-TEMU-AMZ单号合并.xlsx", index=False)
print(f'仓库发货和多渠道发货订单数据合并的数据：{len(pr)} 条')

# # 将第四步合并的订单，合并到TEMU结算数据中
order_list = pd.read_excel(f"{p_path}/处理过的数据/4.领星-TEMU-AMZ单号合并.xlsx")
settled_list = pd.read_excel(f"{p_path}/处理过的数据/3.处理过的结算数据.xlsx")
#
result_list = pd.merge(settled_list, pr, how='left', left_on=['订单编号', 'SKU货号'], right_on=['平台单号', 'MSKU'])
result_list = result_list.drop_duplicates()

# print(result_list.columns)

result_list.drop(['平台单号', 'SKU货号'], axis=1, inplace=True)
result_list.to_excel(f"{p_path}/处理过的数据/4.处理过的结算数据.xlsx", index=False)

print(f'合并到结算数据中的数据：{len(result_list)} 条')
